/**
 * JAVACC DEMO 1.0
 */
package com.apache.excp.core.impl.helper;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.StringReader;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.CellType;
import jxl.Range;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.StringUtils;

import freemarker.cache.StringTemplateLoader;
import freemarker.template.TemplateException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * description:  执行execl导出操作实现类
 * @author Hou Dayu 创建时间：2016-7-28
 */
public class ReportEnginer {

	private Logger log = LoggerFactory.getLogger(ReportEnginer.class);
	//public static final String DIRECTIVE_NUMBER = "?number";
	//public static final String DIRECTIVE_IMAGE = "?image";
	public static final String[] ftlTags = { "${", "<#", "</#" };
	private static final String REMOVE_ROW = "1";
	private static final String REMOVE_COLUMN = "2";
	private static final String NOT_REMOVE_ROW = "3";
	private static final String NOT_REMOVE_COLUMN = "4";
	private static final String FTL_ROW_OR_COLUMN = "5";
	private static final String LINE_SEPARATOR = System.getProperty("line.separator");
	private Map<String, String> inListTagCells = new LinkedHashMap<String, String>();

	/**
	 * description:  执行execl导出操作
	 * @param templateFile 模板文件
	 * @param context      数据模型
	 * @param destFile     需要生成的文件
	 * @throws Exception   
	 * @author Hou Dayu  
	 * @update 2014-7-29
	 */
	public void excute(String templateFile, Map<String, Object> context, String destFile) throws Exception {
		InputStream input = null;
		OutputStream out = null;
		try {
			input = new FileInputStream(templateFile);
			out = new FileOutputStream(destFile);
			excute(input, context, out);
		} finally {
			if (input != null) {
				input.close();
			}
			if (out != null)
				out.close();
		}
	}

	/**
	 * description:  执行execl导出操作
	 */
	public void excute(InputStream input, Map<String, Object> context, OutputStream out) throws Exception {
		Workbook workbook = null;
		WritableWorkbook writableWorkbook = null;
		try {
			workbook = Workbook.getWorkbook(input);
			WorkbookSettings settings = new WorkbookSettings();
			settings.setWriteAccess(null);
			writableWorkbook = Workbook.createWorkbook(out, workbook, settings);
			WritableSheet[] sheets = writableWorkbook.getSheets();
			for (int i = 0; i < sheets.length; i++) {
				WritableSheet writableSheet = sheets[i];
				//try {
				prepare(writableSheet);

				String ftl = convertSheetAsFtl(writableSheet);
				if (StringUtils.isBlank(ftl)) {
					log.debug(writableSheet.getName() + " no freemarker tag context");
					//cleanup(writableSheet);
					continue;
				}
				log.debug(ftl);

				String ftlResult = convertFtl(context, ftl.trim(), writableSheet.getName());
				if (StringUtils.isBlank(ftlResult)) {
					log.debug(writableSheet.getName() + " no out context");
					//cleanup(writableSheet);
					continue;
				}
				exportResultToSheet(writableSheet, ftlResult.trim());
				//} finally {
				//cleanup(writableSheet);
				//}
				cleanup(writableSheet);
			}
		} finally {
			if (workbook != null)
				try {
					workbook.close();
				} catch (Exception localException) {
				}
			if (writableWorkbook != null) {
				writableWorkbook.write();
				writableWorkbook.close();
			}
		}
	}

	private void prepare(WritableSheet writableSheet) {
		writableSheet.insertRow(0);
		writableSheet.insertColumn(0);
		log.debug("prepare");
	}

	private void cleanup(WritableSheet writableSheet) {
		writableSheet.removeRow(0);
		writableSheet.removeColumn(0);
		log.debug("clean up");
	}

	/**
	 * description:  便利模板内容
	 * @param writableSheet  execl工作区
	 * @return
	 * @throws RowsExceededException
	 * @throws WriteException   
	 * @author Hou Dayu  
	 * @update 2014-7-29
	 */
	private String convertSheetAsFtl(WritableSheet writableSheet) throws RowsExceededException, WriteException {
		Map<Integer, List<WritableCell>> tagCells = new LinkedHashMap<Integer, List<WritableCell>>();

		int rows = writableSheet.getRows();
		int columns = writableSheet.getColumns();

		StringBuffer ftl = new StringBuffer();

		int listStartCounter = 0;
		int listEndCounter = 0;
		int ifStartCounter = 0;
		int ifEndCounter = 0;

		for (int row = 0; row < rows; row++) {
			List<WritableCell> rowTags = null;
			StringBuffer rowFtl = new StringBuffer();
			rowStart(row, rowFtl);
			for (int column = 0; column < columns; column++) {
				WritableCell writableCell = writableSheet.getWritableCell(column, row);
				String contents = writableCell.getContents();
				boolean listStart = isListStartTag(contents);
				boolean listEnd = isListEndTag(contents);
				boolean ifStart = isIfStartTag(contents);
				boolean ifEnd = isIfEndTag(contents);
				if (listStart) {
					listStartCounter++;
				}
				if (listEnd) {
					listEndCounter++;
				}
				if (ifStart) {
					ifStartCounter++;
				}
				if (ifEnd) {
					ifEndCounter++;
				}

				if ((listStart) || (listEnd) || (ifStart) || (ifEnd)) {
					rowFtl.append(contents);
					if (rowTags == null) {
						rowTags = new ArrayList<WritableCell>();
					}
					rowTags.add(writableCell);
				} else {
					parseCell(writableSheet, writableCell, row, column, rowFtl, listStartCounter > listEndCounter,
							ifStartCounter > ifEndCounter);
				}
			}
			rowEnd(row, rowFtl);

			if (rowTags != null) {
				tagCells.put(Integer.valueOf(row), rowTags);
			}

			if (rowHasContents(row, rowFtl)) {
				ftl.append(rowFtl);
				lineEnd(ftl);
			}
		}

		markRemoveRowClumn(writableSheet, tagCells);

		return ftl.toString();
	}

	/**
	 * description:  与freemarker结合内容
	 */
	public String convertFtl(Map<String, Object> context, String ftl, String id) throws TemplateException, IOException {
		StringTemplateLoader stringTemplateLoader = new StringTemplateLoader();
		stringTemplateLoader.putTemplate(id, ftl);

		StringWriter stringWriter = FreemarkerHelper.produceAsStringWriter(stringTemplateLoader, context, "UTF-8",
				null, id, "UTF-8");
		if (stringWriter != null) {
			return stringWriter.toString();
		}
		return null;
	}

	/**
	 * description:  将结果生成到execl的工作区中
	 */
	private void exportResultToSheet(WritableSheet writableSheet, String result) throws RowsExceededException,
			NumberFormatException, WriteException, IOException, Exception {
		BufferedReader in = new BufferedReader(new StringReader(result));
		String line = null;
		int toRow = -1;
		Map<Integer, Integer> firstFilledRows = new LinkedHashMap<Integer, Integer>();
		Map<Integer, Integer> firstFilledColumns = new LinkedHashMap<Integer, Integer>();
		Map<String, Integer> copyRows = new LinkedHashMap<String, Integer>();
		Map<String, Integer> copyColumns = new LinkedHashMap<String, Integer>();
		Map<String, String> mergeCells = new LinkedHashMap<String, String>();

		while ((line = in.readLine()) != null) {
			int startInd = line.indexOf("<r_");
			if (startInd == -1) {
				log.debug(line + " is not a row !");
			} else {
				int startEndInd = line.indexOf(">");
				int orgRow = Integer.parseInt(line.substring(startInd + 3, startEndInd));

				String endTag = "</r_" + orgRow + ">";
				int newRow = orgRow + getInsertBeforeRows(copyRows, orgRow).intValue();

				if (toRow < newRow) {
					toRow = newRow;
					toRow = computeToRow(writableSheet, toRow);
				} else {
					Integer fromRow = (Integer) firstFilledRows.get(Integer.valueOf(orgRow));
					if (fromRow == null) {
						fromRow = Integer.valueOf(orgRow);
					}

					toRow++;

					Range[] mergedCell = writableSheet.getMergedCells();//.getMergedCells();
					for (int i = 0; i < mergedCell.length; i++) {
						int x1 = mergedCell[i].getTopLeft().getRow();
						int x2 = mergedCell[i].getBottomRight().getRow();
						if (fromRow.intValue() == x1) {
							toRow += x2 - x1;
							break;
						}

					}

					String key = fromRow + "-" + toRow;
					if (!copyRows.containsKey(key)) {
						copyRow(writableSheet, mergeCells, fromRow.intValue(), toRow);
						copyRows.put(key, Integer.valueOf(toRow));
					}
				}

				int endInd = line.indexOf(endTag);

				line = line.substring(startEndInd + 1, endInd);

				if (firstFilledRows.get(Integer.valueOf(orgRow)) == null) {
					firstFilledRows.put(Integer.valueOf(orgRow), Integer.valueOf(toRow));
				}
				exportLineToRow(writableSheet, mergeCells, line, toRow, copyColumns, firstFilledColumns);
			}
		}
		mergeCells(writableSheet, mergeCells);
		removeRowColumn(writableSheet, copyRows, copyColumns);
	}

	private void mergeCells(WritableSheet writableSheet, Map<String, String> mergeCells) throws RowsExceededException,
			NumberFormatException, WriteException {
		for (Iterator<String> iterator = mergeCells.values().iterator(); iterator.hasNext();) {
			String str = (String) iterator.next();
			String[] arr = str.split(",");
			if (arr.length == 4)
				writableSheet.mergeCells(Integer.parseInt(arr[1]), Integer.parseInt(arr[0]), Integer.parseInt(arr[3]),
						Integer.parseInt(arr[2]));
		}
	}

	private void exportLineToRow(WritableSheet writableSheet, Map<String, String> mergeCells, String line, int toRow,
			Map<String, Integer> copyColumns, Map<Integer, Integer> firstFilledColumns) throws Exception {
		int toColumn = -1;

		int startInd = -1;
		while ((startInd = line.indexOf("<c_")) != -1) {
			int startEndInd = line.indexOf(">");
			String colStr = line.substring(startInd + 3, startEndInd);
			int orgColumn = 0;
			try {
				orgColumn = Integer.parseInt(colStr);
			} catch (Exception e) {
				log.info(toRow + " : " + line);
				log.error(e.getMessage());
				throw e;
			}
			String endTag = "</c_" + orgColumn + ">";
			int newColumn = orgColumn + getInsertBeforeColumns(copyColumns, orgColumn);

			if (toColumn < newColumn) {
				toColumn = newColumn;
				toColumn = computeToColumn(writableSheet, toColumn);
			} else {
				Integer fromColumn = (Integer) firstFilledColumns.get(Integer.valueOf(orgColumn));
				if (fromColumn == null) {
					fromColumn = Integer.valueOf(orgColumn);
				}

				toColumn++;

				Range[] mergedCell = (Range[]) writableSheet.getMergedCells();
				for (int i = 0; i < mergedCell.length; i++) {
					int y1 = mergedCell[i].getTopLeft().getColumn();
					int y2 = mergedCell[i].getBottomRight().getColumn();
					if (fromColumn.intValue() == y1) {
						toColumn += y2 - y1;
						break;
					}

				}

				String key = fromColumn + "-" + toColumn;
				if (!copyColumns.containsKey(key)) {
					copyColumn(writableSheet, mergeCells, fromColumn.intValue(), toColumn);
					copyColumns.put(key, Integer.valueOf(toColumn));
				}
			}

			if (firstFilledColumns.get(Integer.valueOf(orgColumn)) == null) {
				firstFilledColumns.put(Integer.valueOf(orgColumn), Integer.valueOf(toColumn));
			}

			int endInd = line.indexOf(endTag);
			String contents = line.substring(startEndInd + 1, endInd);
			exportCell(writableSheet, mergeCells, toRow, toColumn, contents);

			if (line.length() <= endInd + endTag.length())
				break;
			line = line.substring(endInd + endTag.length());
		}

		int columns = writableSheet.getColumns();
		for (int col = 0; col < columns; col++) {
			WritableCell writableCell = writableSheet.getWritableCell(col, toRow);
			boolean ftlTag = StringUtils.indexOfAny(writableCell.getContents(), ftlTags) != -1;
			if (ftlTag)
				setCell(writableSheet, mergeCells, toRow, col, "");
		}
	}

	private int computeToColumn(WritableSheet writableSheet, int toColumn) {
		int cols = writableSheet.getColumns();
		int i = toColumn;
		while (i < cols) {
			Cell cell = writableSheet.getCell(i, 0);

			if (!CellType.LABEL.equals(cell.getType())) {
				break;
			}
			if (!REMOVE_COLUMN.equals(cell.getContents())) {
				break;
			}
			i++;
		}
		return i;
	}

	private int computeToRow(WritableSheet writableSheet, int toRow) {
		int rows = writableSheet.getRows();
		int i = toRow;
		while (i < rows) {
			Cell cell = writableSheet.getCell(0, i);

			if (!CellType.LABEL.equals(cell.getType())) {
				break;
			}
			if (!REMOVE_ROW.equals(cell.getContents())) {
				break;
			}
			i++;
		}
		return i;
	}

	private void exportCell(WritableSheet writableSheet, Map<String, String> mergeCells, int row, int column,
			String contents) throws Exception {
		setCell(writableSheet, mergeCells, row, column, contents);

		setCell(writableSheet, mergeCells, row, 0, NOT_REMOVE_ROW);

		setCell(writableSheet, mergeCells, 0, column, NOT_REMOVE_COLUMN);
	}

	private boolean setCell(WritableSheet writableSheet, Map<String, String> mergeCells, int row, int column,
			String contents) throws RowsExceededException, WriteException {
		WritableCell writableCell = writableSheet.getWritableCell(column, row);
		if (isNumberDirective(contents)) {
			if (setNumberCell(writableSheet, row, column, contents)) {
				return true;
			}
			contents = StringUtils.substringBeforeLast(contents, "?");
		}

		if (isImageDirective(contents)) {
			if (setImageCell(writableSheet, mergeCells, row, column, contents)) {
				return true;
			}
			contents = StringUtils.substringBeforeLast(contents, "?");
		}

		if (CellType.LABEL.equals(writableCell.getType())) {
			((Label) writableCell).setString(contents);
		} else {
			Label newWritableCell = new Label(column, row, contents);
			if (writableCell.getWritableCellFeatures() != null) {
				newWritableCell.setCellFeatures(writableCell.getWritableCellFeatures());
			}
			if (writableCell.getCellFormat() != null) {
				newWritableCell.setCellFormat(writableCell.getCellFormat());
			}
			writableSheet.addCell(newWritableCell);
		}
		return true;
	}

	private boolean setNumberCell(WritableSheet writableSheet, int row, int column, String contents)
			throws RowsExceededException, WriteException {
		try {
			WritableCell writableCell = writableSheet.getWritableCell(column, row);
			int start = contents.indexOf("?");
			String num = contents.substring(0, start).trim();
			double val = Double.parseDouble(num);
			if (CellType.NUMBER.equals(writableCell.getType())) {
				((Number) writableCell).setValue(val);
			} else {
				Number newWritableCell = new Number(column, row, val);
				if (writableCell.getWritableCellFeatures() != null) {
					newWritableCell.setCellFeatures(writableCell.getWritableCellFeatures());
				}
				if (writableCell.getCellFormat() != null) {
					newWritableCell.setCellFormat(writableCell.getCellFormat());
				}
				writableSheet.addCell(newWritableCell);
			}
			return true;
		} catch (NumberFormatException ne) {
			log.error("Error: " + row + "," + column + ":" + contents + ne.getMessage());
		}
		return false;
	}

	private boolean setImageCell(WritableSheet writableSheet, Map<String, String> mergeCells, int row, int column,
			String contents) throws RowsExceededException, WriteException {
		int start = contents.indexOf("?");
		String filePath = contents.substring(0, start).trim();
		filePath = StringUtils.replace(filePath, "\\", "/");
		if (!StringUtils.endsWithIgnoreCase(filePath, ".png")) {
			log.warn("Only support png image file : " + filePath);
			return false;
		}

		File file = new File(filePath);
		if (!file.exists()) {
			log.warn("Not found file " + filePath);
			return false;
		}
		if (file.isDirectory()) {
			log.warn("Excepted a png image file,but got a directory : " + filePath);
			return false;
		}

		int width = 1;
		int height = 1;

		for (Iterator<String> iterator = mergeCells.values().iterator(); iterator.hasNext();) {
			String str = (String) iterator.next();
			String[] arr = str.split(",");
			if (arr.length == 4) {
				int x1 = Integer.parseInt(arr[0]);
				int y1 = Integer.parseInt(arr[1]);
				if ((x1 == row) && (y1 == column)) {
					int x2 = Integer.parseInt(arr[2]);
					int y2 = Integer.parseInt(arr[3]);
					width = x2 - x1 + 1;
					height = y2 - y1 + 1;
					break;
				}
			}
		}

		Range[] mergedCell = writableSheet.getMergedCells();
		for (int i = 0; i < mergedCell.length; i++) {
			int x1 = mergedCell[i].getTopLeft().getRow();
			int y1 = mergedCell[i].getTopLeft().getColumn();
			if ((x1 == row) && (y1 == column)) {
				int x2 = mergedCell[i].getBottomRight().getRow();
				int y2 = mergedCell[i].getBottomRight().getColumn();
				width = x2 - x1 + 1;
				height = y2 - y1 + 1;
				break;
			}
		}

		WritableImage writableImage = new WritableImage(column - 1, row - 1, height, width, file);
		writableSheet.addImage(writableImage);

		return true;
	}

	private boolean isNumberDirective(String contents) {
		if ((contents != null) && (contents.lastIndexOf("?number") != -1)) {
			return true;
		}
		return false;
	}

	private boolean isImageDirective(String contents) {
		if ((contents != null) && (contents.lastIndexOf("?image") != -1)) {
			return true;
		}
		return false;
	}

	private Integer getInsertBeforeRows(Map<String, Integer> copyRows, int row) {
		int result = 0;
		for (Iterator<String> iterator = copyRows.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			String[] arr = key.split("-");
			int org = Integer.parseInt(arr[0]);
			if (org < row) {
				result++;
			}
		}
		if (result > 0)
			log.debug(" ====================== row " + row + " \u524D\u9762\u589E\u52A0\u4E86  " + result);
		return Integer.valueOf(result);
	}

	private int getInsertBeforeColumns(Map<String, Integer> copyColumns, int column) {
		int result = 0;
		for (Iterator<String> iterator = copyColumns.keySet().iterator(); iterator.hasNext();) {
			String key = (String) iterator.next();
			String[] arr = key.split("-");
			int org = Integer.parseInt(arr[0]);
			if (org < column) {
				result++;
			}
		}
		if (result > 0)
			log.debug(" ====================== column " + column + " \u524D\u9762\u589E\u52A0\u4E86  " + result);
		return result;
	}

	private void cleanCellContents(WritableCell cell) {
		if (CellType.LABEL.equals(cell.getType()))
			((Label) cell).setString("");
		else if (CellType.NUMBER.equals(cell.getType()))
			((Number) cell).setValue(0.0D);
	}

	private void copyRow(WritableSheet writableSheet, Map<String, String> mergeCells, int from, int to)
			throws RowsExceededException, WriteException {
		writableSheet.insertRow(to);
		int columns = writableSheet.getColumns();
		for (int col = 0; col < columns; col++) {
			WritableCell writableCell = writableSheet.getWritableCell(col, from);
			WritableCell newWritableCell = writableCell.copyTo(col, to);
			writableSheet.addCell(newWritableCell);
			if (inListTagCells.get(writableSheet.getName() + "," + from + "," + col) != null) {
				cleanCellContents(newWritableCell);
				inListTagCells.put(writableSheet.getName() + "," + to + "," + col, "");
			}
		}

		Range[] mergedCell = writableSheet.getMergedCells();
		for (int i = 0; i < mergedCell.length; i++) {
			int x1 = mergedCell[i].getTopLeft().getRow();

			if (from == x1) {
				int y1 = mergedCell[i].getTopLeft().getColumn();
				int x2 = mergedCell[i].getBottomRight().getRow();
				int y2 = mergedCell[i].getBottomRight().getColumn();

				int newX1 = to;
				int newY1 = y1;
				int newX2 = newX1 + (x2 - x1);
				int newY2 = y2;

				String key = newX1 + "," + newY1 + "," + newX2 + "," + newY2;
				mergeCells.put(key, key);

				for (int ind = newX1; ind <= newX2; ind++) {
					setCell(writableSheet, mergeCells, ind, 0, NOT_REMOVE_ROW);
				}
				for (int ind = newY1; ind <= newY2; ind++)
					setCell(writableSheet, mergeCells, 0, ind, NOT_REMOVE_COLUMN);
			}
		}
	}

	private void copyColumn(WritableSheet writableSheet, Map<String, String> mergeCells, int from, int to)
			throws RowsExceededException, WriteException {
		writableSheet.insertColumn(to);

		int rows = writableSheet.getRows();
		for (int row = 0; row < rows; row++) {
			WritableCell writableCell = writableSheet.getWritableCell(from, row);
			WritableCell newWritableCell = writableCell.copyTo(to, row);
			writableSheet.addCell(newWritableCell);
			if (inListTagCells.get(writableSheet.getName() + "," + row + "," + from) != null) {
				cleanCellContents(newWritableCell);
				inListTagCells.put(writableSheet.getName() + "," + row + "," + to, "");
			}
		}

		Range[] mergedCell = writableSheet.getMergedCells();
		for (int i = 0; i < mergedCell.length; i++) {
			int y1 = mergedCell[i].getTopLeft().getColumn();

			if (from == y1) {
				int x1 = mergedCell[i].getTopLeft().getRow();
				int x2 = mergedCell[i].getBottomRight().getRow();
				int y2 = mergedCell[i].getBottomRight().getColumn();

				int newX1 = x1;
				int newY1 = to;
				int newX2 = x2;
				int newY2 = to + (y2 - y1);

				String key = newX1 + "," + newY1 + "," + newX2 + "," + newY2;
				mergeCells.put(key, key);

				for (int ind = newX1; ind <= newX2; ind++) {
					setCell(writableSheet, mergeCells, ind, 0, NOT_REMOVE_ROW);
				}
				for (int ind = newY1; ind <= newY2; ind++) {
					setCell(writableSheet, mergeCells, 0, ind, NOT_REMOVE_COLUMN);
				}
			}
		}
		log.debug(writableSheet.getName() + " copy column " + from + " to " + to);
	}

	private void markRemoveRowClumn(WritableSheet writableSheet, Map<Integer, List<WritableCell>> tagCells)
			throws RowsExceededException, WriteException {
		if (tagCells == null) {
			return;
		}

		for (Iterator<Integer> iterator = tagCells.keySet().iterator(); iterator.hasNext();) {
			Integer row = (Integer) iterator.next();
			List<WritableCell> rowTags = (List<WritableCell>) tagCells.get(row);
			if (rowTags.size() != 0) {
				if (rowTags.size() == 1) {
					setCell(writableSheet, null, row.intValue(), 0, REMOVE_ROW);

					log.debug(writableSheet.getName() + " row " + row + " is freemarker tag");
				} else {
					if (rowTags.size() % 2 == 1) {
						throw new RuntimeException("row  " + row + " : tag error \u6CA1\u6709\u6B63\u786E\u7ED3\u675F");
					}
					int listEndTagCounter = 0;
					int ifEndTagCounter = 0;
					for (WritableCell writableCell : rowTags) {
						if (isListEndTag(writableCell.getContents())) {
							listEndTagCounter++;
						}
						if (isIfEndTag(writableCell.getContents())) {
							ifEndTagCounter++;
						}
					}
					if (((listEndTagCounter > 0) && (listEndTagCounter != rowTags.size() / 2))
							|| ((ifEndTagCounter > 0) && (ifEndTagCounter != rowTags.size() / 2))) {
						throw new RuntimeException("row  " + row
								+ " : tag error \u5F00\u59CB\u548C\u7ED3\u675F\u4E2A\u6570\u4E0D\u5339\u914D");
					}
					for (WritableCell writableCell : rowTags) {
						int column = writableCell.getColumn();
						setCell(writableSheet, null, 0, column, REMOVE_COLUMN);
						log.debug(writableSheet.getName() + " column " + column + " is freemarker tags");
					}
				}
			}
		}
	}

	private void removeRowColumn(WritableSheet writableSheet, Map<String, Integer> copyRows,
			Map<String, Integer> copyColumns) {
		int rows = writableSheet.getRows();
		int columns = writableSheet.getColumns();

		List<Integer> delRows = new ArrayList<Integer>();
		List<Integer> delColumns = new ArrayList<Integer>();

		for (int i = 1; i < columns; i++) {
			Cell cell = writableSheet.getCell(i, 0);
			if (CellType.LABEL.equals(cell.getType())) {
				if (!StringUtils.isBlank(cell.getContents())) {
					if (!NOT_REMOVE_COLUMN.equals(cell.getContents())) {
						writableSheet.removeColumn(i);
						delColumns.add(Integer.valueOf(i));
						log.debug(writableSheet.getName() + " remove column " + i);
						i--;
					}
				}
			}
		}
		for (int i = 1; i < rows; i++) {
			Cell cell = writableSheet.getCell(0, i);
			if (CellType.LABEL.equals(cell.getType())) {
				if (!StringUtils.isBlank(cell.getContents())) {
					if (!NOT_REMOVE_ROW.equals(cell.getContents())) {
						writableSheet.removeRow(i);
						delRows.add(Integer.valueOf(i));
						log.debug(writableSheet.getName() + " remove row " + i);
						i--;
					}
				}
			}
		}
		int i = 0;
		if ((delRows.size() != 0) || (delColumns.size() != 0))
			try {
				while (true) {
					WritableImage image = writableSheet.getImage(i);
					if (image != null) {
						double r = image.getY();
						double c = image.getX();

						int cutR = 0;
						for (Integer delR : delRows) {
							if (delR.intValue() <= r) {
								cutR++;
							}
						}
						if (cutR != 0) {
							image.setY(r - cutR);
						}

						int cutC = 0;
						for (Integer delC : delColumns) {
							if (delC.intValue() <= c) {
								cutC++;
							}
						}
						if (cutC != 0) {
							image.setX(c - cutC);
						}
					}

					i++;
				}
			} catch (Exception localException) {
			}
	}

	private void parseCell(WritableSheet writableSheet, WritableCell writableCell, int row, int column,
			StringBuffer ftl, boolean inListTag, boolean inIfTag) throws RowsExceededException, WriteException {
		if (CellType.LABEL.equals(writableCell.getType())) {
			String contents = writableCell.getContents();
			boolean ftlTag = StringUtils.indexOfAny(contents, ftlTags) != -1;
			if ((inListTag) || (inIfTag) || (ftlTag)) {
				cellStart(row, column, ftl);
				ftl.append(contents);
				if ((inListTag) && (ftlTag)) {
					inListTagCells.put(
							writableSheet.getName() + "," + writableCell.getRow() + "," + writableCell.getColumn(), "");
				}
				cellEnd(row, column, ftl);
				setCell(writableSheet, null, row, 0, FTL_ROW_OR_COLUMN);
				setCell(writableSheet, null, 0, column, FTL_ROW_OR_COLUMN);
			}
		}
	}

	/**
	 * description:  列表标记开始
	 */
	private boolean rowHasContents(int row, StringBuffer rowFtl) {
		StringBuffer _temp = new StringBuffer();
		rowStart(row, _temp);
		rowEnd(row, _temp);
		if (rowFtl.toString().equals(_temp.toString())) {
			return false;
		}
		int len = ("<r_" + row + ">").length();
		if ((isListStartTag(rowFtl.toString())) || (isListEndTag(rowFtl.toString()))
				|| (isIfStartTag(rowFtl.toString())) || (isIfEndTag(rowFtl.toString()))) {
			rowFtl.delete(0, len);
			rowFtl.delete(rowFtl.length() - len - 1, rowFtl.length());
		}
		return true;
	}

	/**
	 * description:  列表标记开始
	 */
	private boolean isListStartTag(String contents) {
		if ((contents.indexOf("<#list ") != -1)
				&& (StringUtils.countMatches(contents, "<#list ") != StringUtils.countMatches(contents, "</#list>"))) {
			return true;
		}
		return false;
	}

	/**
	 * description:  列表标记结束
	 */
	private boolean isListEndTag(String contents) {
		if ((contents.indexOf("</#list>") != -1)
				&& (StringUtils.countMatches(contents, "<#list ") != StringUtils.countMatches(contents, "</#list>"))) {
			return true;
		}
		return false;
	}

	/**
	 * description:  判断标记开始
	 */
	private boolean isIfStartTag(String contents) {
		if ((contents.indexOf("<#if ") != -1)
				&& (StringUtils.countMatches(contents, "<#if ") != StringUtils.countMatches(contents, "</#if>"))) {
			return true;
		}
		return false;
	}

	/**
	 * description:  判断标记结束
	 */
	private boolean isIfEndTag(String contents) {
		if ((contents.indexOf("</#if>") != -1)
				&& (StringUtils.countMatches(contents, "<#if ") != StringUtils.countMatches(contents, "</#if>"))) {
			return true;
		}
		return false;
	}

	/**
	 * description:  列开始
	 */
	private void cellStart(int row, int column, StringBuffer ftl) {
		ftl.append("<c_").append(column).append(">");
	}

	/**
	 * description:  列结束
	 */
	private void cellEnd(int row, int column, StringBuffer ftl) {
		ftl.append("</c_").append(column).append(">");
	}

	/**
	 * description:  行开始
	 */
	private void rowStart(int row, StringBuffer ftl) {
		ftl.append("<r_").append(row).append(">");
	}

	/**
	 * description:  行结束
	 */
	private void rowEnd(int row, StringBuffer ftl) {
		ftl.append("</r_").append(row).append(">");
	}

	private void lineEnd(StringBuffer ftl) {
		ftl.append(LINE_SEPARATOR);
	}
}
